﻿using System;
using System.Data;
using System.Collections.Generic;
using Chenxin.Lib.Business.Entity;
namespace Chenxin.Lib.Business.BLL
{
    /// <summary>
    /// 业务逻辑类ContractFinanceBillInfo 的摘要说明。
    /// </summary>
    public class ContractFinanceBillInfo
    {
        public ContractFinanceBillInfo()
        { }
        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static void Add(Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model)
        {
            Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.Add(model);
        }

        /// <summary>
        /// 获得开单编号
        /// </summary>
        /// <param name="CompanyId">公司ID</param>
        /// <returns></returns>
        public static string GenerateCode(Guid CompanyId)
        {
            return Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GenerateCode(CompanyId);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model)
        {
            Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.Update(model);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public static void Delete(Guid BillId)
        {

            Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.Delete(BillId);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Chenxin.Lib.Business.Entity.ContractFinanceBillInfo GetModel(Guid BillId)
        {

            return Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetModel(BillId);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            return Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetList(strWhere);
        }

        public static DataSet GetListByConditions(Dictionary<string, string> dicParams)
        { 
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;

            string TableName = "t_Contract_Finance_BillInfo Left Join t_Contract_BaseInfo On(t_Contract_Finance_BillInfo.ContractId = t_Contract_BaseInfo.ContractId)";
            string OrderField = "t_Contract_Finance_BillInfo.BillDate DESC";
            string FieldList = "BillId,IsFinish,IsRecheck,AreaName,BillDepartName,Status,ContractCode,ContractCompany,HouseAddress,CustomerName,FundName,BillCode,BillDate,TBillMoney,RecMoney,Payment,PayMoney,PayDate," +
                               "PayOprEmpName,InvoiceCode,BusinessType,AchievementDate,InvoiceMemo,BillPlace,PayPlace,InOrOut,InvoiceDate";
            string TableFieldList = "BillId,IsFinish,IsRecheck,(Select DepartName From Chenxin_Main.dbo.Hr_Struct_DepartTreeRelation Left Join Chenxin_Main.dbo.Hr_DepartmentInfo On(DepartId = ParentId) Where ChildId = BillDepartId) As AreaName," +
                                    "BillDepartName,(Case When t_Contract_Finance_BillInfo.Status = 1 And InOrOut = 1 Then '已收' When t_Contract_Finance_BillInfo.Status = 1 And InOrOut = -1 Then '已付' When t_Contract_Finance_BillInfo.Status = 0 And InOrOut = 1 Then '未收' When t_Contract_Finance_BillInfo.Status = 0 And InOrOut = -1 Then '未付' Else '作废' End)As Status,t_Contract_Finance_BillInfo.ContractCode,ContractCompany,HouseAddress,CustomerName,FundName,BillCode," +
                                    "REPLACE(CONVERT(Varchar(10),BillDate,111),'/','-') As BillDate, BillMoney As TBillMoney,(Case When InOrOut =1 Then Isnull(Cast(PayMoney As Varchar),'0.00') Else '-' End) As RecMoney,(Case When InOrOut =-1 Then Isnull(Cast(PayMoney As Varchar),'0.00') Else '-' End) As Payment,(Cast(PayMoney As Varchar) + '元') As PayMoney," +
                                    "REPLACE(CONVERT(Varchar(10),PayDate,111),'/','-') As PayDate,PayOprEmpName,InvoiceCode,(Case When t_Contract_BaseInfo.BusinessType = 10 Then '买卖' When t_Contract_BaseInfo.BusinessType = 20 Then '出租' When t_Contract_BaseInfo.BusinessType = 30 Then '零散业务' Else '其他业务' End) As BusinessType,AchievementDate,InvoiceMemo,BillPlace,PayPlace,InOrOut,InvoiceDate";

            strWhere.Append(" 1=1 ");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            ////获取当前时间
            //DateTime now = DateTime.Now;
            //DateTime d1 = new DateTime(now.Year, now.Month, 1);
            //DateTime d2 = d1.AddMonths(1).AddDays(-1);
            string datetype = "";
            if (dicParams.ContainsKey("datetype"))
            {
                switch (dicParams["datetype"])
                {
                    case "10":  //开单日期
                        datetype = "BillDate";
                        break;
                    case "20":  //收付款日期
                        datetype = "PayDate";
                        break;
                    case "30":  //实收归属
                        datetype = "cast(case when len(ISNULL(t_Contract_Finance_BillInfo.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(t_Contract_Finance_BillInfo.AchievementDate, '1900.1') end +'.1' as datetime)";
                        break;
                    case "40":  //业绩归属
                        datetype = "t_Contract_BaseInfo.ArchivementDate";
                        break;
                }
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And {1}>='{0}'", dicParams["StartTime"],datetype);
            }
            //else
            //    strWhere.AppendFormat(" And BillDate>='{0}'", d1);
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And {1}<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"),datetype);
            }
            //else
            //    strWhere.AppendFormat(" And BillDate<'{0}'", d2);
            if (dicParams.ContainsKey("Status"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.Status = {0}", dicParams["Status"]);
            }
            else
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.Status > {0}", "-1");
            }
            if (dicParams.ContainsKey("FundName"))
            {
                strWhere.AppendFormat(" And FundName In ({0})", dicParams["FundName"]);
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (BillCode Like '%{0}%' Or t_Contract_Finance_BillInfo.ContractCode Like '%{0}%')", dicParams["Keyword"]);
            }
            if (dicParams.ContainsKey("BillPlace"))
            {
                strWhere.AppendFormat(" And BillPlace = '{0}'", dicParams["BillPlace"]);
            }
            if (dicParams.ContainsKey("inorout"))
            {
                strWhere.AppendFormat(" And inorout = '{0}'", dicParams["inorout"]);
            }
            if (dicParams.ContainsKey("IsFinish"))
            {
                strWhere.AppendFormat(" And IsFinish = '{0}'", dicParams["IsFinish"]);
            }
            if (dicParams.ContainsKey("IsRecheck"))
            {
                strWhere.AppendFormat(" And IsRecheck = '{0}'", dicParams["IsRecheck"]);
            }
            if (dicParams.ContainsKey("PayPlace"))
            {
                strWhere.AppendFormat(" And PayPlace = '{0}'", dicParams["PayPlace"]);
            }
            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.BillCityId = '{0}'", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.BillCompanyId = '{0}'", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = BillDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And BillDepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" And BillDepartId in (select DepartId from t_Business_EmpSetting where UserId='{0}' and SearchBill=1)", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.Append(" And (1=0)");
            }

            DataSet ds = Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetPartList(PageSize, PageIndex, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);

            //***********获得总合计数据**************************
            string strSql = "select sum(BillMoney) as TBillMoney,sum(Case When InOrOut =1 Then Isnull(PayMoney,0.00) Else 0 End) As RecMoney,sum(Case When InOrOut =-1 Then Isnull(PayMoney,0.00) Else 0 End) As Payment from " + TableName + " where " + strWhere.ToString();
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["AreaName"] = "总合计";
            dr["TBillMoney"] = ds_count.Tables[0].Rows[0]["TBillMoney"];
            dr["RecMoney"] = ds_count.Tables[0].Rows[0]["RecMoney"];
            dr["Payment"] = ds_count.Tables[0].Rows[0]["Payment"];
            ds.Tables[0].Rows.Add(dr);
            //***************************************************

            return ds;
        }

        public static DataSet GetListByExport(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();

            string TableName = "t_Contract_Finance_BillInfo Left Join t_Contract_BaseInfo On(t_Contract_Finance_BillInfo.ContractId = t_Contract_BaseInfo.ContractId)";
            string OrderField = "t_Contract_Finance_BillInfo.BillDate DESC";
            //string FieldList = "BillId,IsFinish,IsRecheck,AreaName,BillDepartName,Status,ContractCode,ContractCompany,HouseAddress,CustomerName,FundName,BillCode,BillDate,TBillMoney,RecMoney,Payment,PayMoney,PayDate," +
            //                   "PayOprEmpName,InvoiceCode,BusinessType,AchievementDate,InvoiceMemo,BillPlace,PayPlace,InOrOut";
            string TableFieldList = "BillId,IsFinish,IsRecheck,(Select DepartName From Chenxin_Main.dbo.Hr_Struct_DepartTreeRelation Left Join Chenxin_Main.dbo.Hr_DepartmentInfo On(DepartId = ParentId) Where ChildId = BillDepartId) As AreaName," +
                                    "BillDepartName,(Case When t_Contract_Finance_BillInfo.Status = 1 And InOrOut = 1 Then '已收' When t_Contract_Finance_BillInfo.Status = 1 And InOrOut = -1 Then '已付' When t_Contract_Finance_BillInfo.Status = 0 And InOrOut = 1 Then '未收' When t_Contract_Finance_BillInfo.Status = 0 And InOrOut = -1 Then '未付' Else '作废' End)As Status,t_Contract_Finance_BillInfo.ContractCode,ContractCompany,HouseAddress,CustomerName,FundName,BillCode," +
                                    "REPLACE(CONVERT(Varchar(10),BillDate,111),'/','-') As BillDate, BillMoney As TBillMoney,(Case When InOrOut =1 Then Isnull(Cast(PayMoney As Varchar),'0.00') Else '0' End) As RecMoney,(Case When InOrOut =-1 Then Isnull(Cast(PayMoney As Varchar),'0.00') Else '0' End) As Payment,(Cast(PayMoney As Varchar) + '元') As PayMoney," +
                                    "REPLACE(CONVERT(Varchar(10),PayDate,111),'/','-') As PayDate,PayOprEmpName,InvoiceCode,(Case When t_Contract_BaseInfo.BusinessType = 10 Then '买卖' When t_Contract_BaseInfo.BusinessType = 20 Then '出租' When t_Contract_BaseInfo.BusinessType = 30 Then '零散业务' Else '其他业务' End) As BusinessType,AchievementDate,InvoiceMemo,BillPlace,PayPlace,InOrOut,REPLACE(CONVERT(Varchar(10),t_Contract_Finance_BillInfo.InvoiceDate,111),'/','-') As InvoiceDate";

            strWhere.Append(" 1=1 ");
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            string datetype = "";
            if (dicParams.ContainsKey("datetype"))
            {
                switch (dicParams["datetype"])
                {
                    case "10":  //开单日期
                        datetype = "BillDate";
                        break;
                    case "20":  //收付款日期
                        datetype = "PayDate";
                        break;
                    case "30":  //实收归属
                        datetype = "cast(case when len(ISNULL(t_Contract_Finance_BillInfo.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(t_Contract_Finance_BillInfo.AchievementDate, '1900.1') end +'.1' as datetime)";
                        break;
                    case "40":  //业绩归属
                        datetype = "t_Contract_BaseInfo.ArchivementDate";
                        break;
                }
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And {1}>='{0}'", dicParams["StartTime"], datetype);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And {1}<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"), datetype);
            }
            if (dicParams.ContainsKey("Status"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.Status = {0}", dicParams["Status"]);
            }
            else
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.Status > {0}", "-1");
            }
            if (dicParams.ContainsKey("inorout"))
            {
                strWhere.AppendFormat(" And inorout = '{0}'", dicParams["inorout"]);
            }
            if (dicParams.ContainsKey("IsFinish"))
            {
                strWhere.AppendFormat(" And IsFinish = '{0}'", dicParams["IsFinish"]);
            }
            if (dicParams.ContainsKey("IsRecheck"))
            {
                strWhere.AppendFormat(" And IsRecheck = '{0}'", dicParams["IsRecheck"]);
            }
            if (dicParams.ContainsKey("FundName"))
            {
                strWhere.AppendFormat(" And FundName In ({0})", dicParams["FundName"]);
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (BillCode Like '%{0}%' Or t_Contract_Finance_BillInfo.ContractCode Like '%{0}%')", dicParams["Keyword"]);
            }
            if (dicParams.ContainsKey("BillPlace"))
            {
                strWhere.AppendFormat(" And BillPlace = '{0}'", dicParams["BillPlace"]);
            }
            if (dicParams.ContainsKey("PayPlace"))
            {
                strWhere.AppendFormat(" And PayPlace = '{0}'", dicParams["PayPlace"]);
            }
            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.BillCityId = '{0}'", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And t_Contract_Finance_BillInfo.BillCompanyId = '{0}'", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = BillDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And BillDepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" And BillDepartId in (select DepartId from t_Business_EmpSetting where UserId='{0}' and SearchBill=1)", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.Append(" And (1=0)");
            }

            string strSql = "select " + TableFieldList + " from " + TableName + " where " + strWhere.ToString() + " order by " + OrderField;

            return Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        }


        public static DataSet GetBilledByContractId(string ContractId, string InOrOut, string FundName)
        {
            return Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetBilledByContractId(ContractId, InOrOut, FundName);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo> GetModelList(string strWhere)
        {
            DataSet ds = Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetList(strWhere);
            return DataTableToList(ds.Tables[0]);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo> DataTableToList(DataTable dt)
        {
            List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo> modelList = new List<Chenxin.Lib.Business.Entity.ContractFinanceBillInfo>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Chenxin.Lib.Business.Entity.ContractFinanceBillInfo model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Chenxin.Lib.Business.Entity.ContractFinanceBillInfo();
                    if (dt.Rows[n]["BillId"].ToString() != "")
                    {
                        model.BillId = new Guid(dt.Rows[n]["BillId"].ToString());
                    }
                    if (dt.Rows[n]["ContractId"].ToString() != "")
                    {
                        model.ContractId = new Guid(dt.Rows[n]["ContractId"].ToString());
                    }
                    if (dt.Rows[n]["ContractCityId"].ToString() != "")
                    {
                        model.ContractCityId = new Guid(dt.Rows[n]["ContractCityId"].ToString());
                    }
                    model.ContractCity = dt.Rows[n]["ContractCity"].ToString();
                    if (dt.Rows[n]["ContractCompanyId"].ToString() != "")
                    {
                        model.ContractCompanyId = new Guid(dt.Rows[n]["ContractCompanyId"].ToString());
                    }
                    model.ContractCompany = dt.Rows[n]["ContractCompany"].ToString();
                    model.ContractCode = dt.Rows[n]["ContractCode"].ToString();
                    model.BillCode = dt.Rows[n]["BillCode"].ToString();
                    if (dt.Rows[n]["InOrOut"].ToString() != "")
                    {
                        model.InOrOut = int.Parse(dt.Rows[n]["InOrOut"].ToString());
                    }
                    model.FundName = dt.Rows[n]["FundName"].ToString();
                    model.CustomerName = dt.Rows[n]["CustomerName"].ToString();
                    model.BillUnit = dt.Rows[n]["BillUnit"].ToString();
                    if (dt.Rows[n]["BillMoney"].ToString() != "")
                    {
                        model.BillMoney = decimal.Parse(dt.Rows[n]["BillMoney"].ToString());
                    }
                    if (dt.Rows[n]["BillDate"].ToString() != "")
                    {
                        model.BillDate = DateTime.Parse(dt.Rows[n]["BillDate"].ToString());
                    }
                    if (dt.Rows[n]["Status"].ToString() != "")
                    {
                        model.Status = int.Parse(dt.Rows[n]["Status"].ToString());
                    }
                    model.BillMemo = dt.Rows[n]["BillMemo"].ToString();
                    model.UnusedReason = dt.Rows[n]["UnusedReason"].ToString();
                    if (dt.Rows[n]["UnusedDate"].ToString() != "")
                    {
                        model.UnusedDate = DateTime.Parse(dt.Rows[n]["UnusedDate"].ToString());
                    }
                    if (dt.Rows[n]["UnusedPersonId"].ToString() != "")
                    {
                        model.UnusedPersonId = new Guid(dt.Rows[n]["UnusedPersonId"].ToString());
                    }
                    model.UnusedPerson = dt.Rows[n]["UnusedPerson"].ToString();
                    if (dt.Rows[n]["BillDepartId"].ToString() != "")
                    {
                        model.BillDepartId = new Guid(dt.Rows[n]["BillDepartId"].ToString());
                    }
                    model.BillDepartName = dt.Rows[n]["BillDepartName"].ToString();
                    if (dt.Rows[n]["BillCompanyId"].ToString() != "")
                    {
                        model.BillCompanyId = new Guid(dt.Rows[n]["BillCompanyId"].ToString());
                    }
                    model.BillCompany = dt.Rows[n]["BillCompany"].ToString();
                    if (dt.Rows[n]["BillCityId"].ToString() != "")
                    {
                        model.BillCityId = new Guid(dt.Rows[n]["BillCityId"].ToString());
                    }
                    model.BillCity = dt.Rows[n]["BillCity"].ToString();
                    if (dt.Rows[n]["InputTime"].ToString() != "")
                    {
                        model.InputTime = DateTime.Parse(dt.Rows[n]["InputTime"].ToString());
                    }
                    if (dt.Rows[n]["BillOprEmpId"].ToString() != "")
                    {
                        model.BillOprEmpId = new Guid(dt.Rows[n]["BillOprEmpId"].ToString());
                    }
                    model.BillOprEmpName = dt.Rows[n]["BillOprEmpName"].ToString();
                    if (dt.Rows[n]["BillOprDepartId"].ToString() != "")
                    {
                        model.BillOprDepartId = new Guid(dt.Rows[n]["BillOprDepartId"].ToString());
                    }
                    model.BillOprDepartName = dt.Rows[n]["BillOprDepartName"].ToString();
                    if (dt.Rows[n]["PayDate"].ToString() != "")
                    {
                        model.PayDate = DateTime.Parse(dt.Rows[n]["PayDate"].ToString());
                    }
                    if (dt.Rows[n]["PayMoney"].ToString() != "")
                    {
                        model.PayMoney = decimal.Parse(dt.Rows[n]["PayMoney"].ToString());
                    }
                    if (dt.Rows[n]["RemainMoney"].ToString() != "")
                    {
                        model.RemainMoney = decimal.Parse(dt.Rows[n]["RemainMoney"].ToString());
                    }
                    if (dt.Rows[n]["PayOprEmpId"].ToString() != "")
                    {
                        model.PayOprEmpId = new Guid(dt.Rows[n]["PayOprEmpId"].ToString());
                    }
                    model.PayOprEmpName = dt.Rows[n]["PayOprEmpName"].ToString();
                    if (dt.Rows[n]["PayOprEmpDepartId"].ToString() != "")
                    {
                        model.PayOprEmpDepartId = new Guid(dt.Rows[n]["PayOprEmpDepartId"].ToString());
                    }
                    model.PayOprEmpDepart = dt.Rows[n]["PayOprEmpDepart"].ToString();
                    model.InvoiceCode = dt.Rows[n]["InvoiceCode"].ToString();
                    if (dt.Rows[n]["InvoiceDate"].ToString() != "")
                    {
                        model.InvoiceDate = DateTime.Parse(dt.Rows[n]["InvoiceDate"].ToString());
                    }
                    model.InvoiceMemo = dt.Rows[n]["InvoiceMemo"].ToString();
                    if (dt.Rows[n]["InvoiceOprEmpId"].ToString() != "")
                    {
                        model.InvoiceOprEmpId = new Guid(dt.Rows[n]["InvoiceOprEmpId"].ToString());
                    }
                    model.InvoiceOprEmp = dt.Rows[n]["InvoiceOprEmp"].ToString();
                    if (dt.Rows[n]["InvoiceOprDate"].ToString() != "")
                    {
                        model.InvoiceOprDate = DateTime.Parse(dt.Rows[n]["InvoiceOprDate"].ToString());
                    }
                    if (dt.Rows[n]["IsFinish"].ToString() != "")
                    {
                        if ((dt.Rows[n]["IsFinish"].ToString() == "1") || (dt.Rows[n]["IsFinish"].ToString().ToLower() == "true"))
                        {
                            model.IsFinish = true;
                        }
                        else
                        {
                            model.IsFinish = false;
                        }
                    }
                    model.AchievementDate = dt.Rows[n]["AchievementDate"].ToString();
                    if (dt.Rows[n]["LastCreateArchDate"].ToString() != "")
                    {
                        model.LastCreateArchDate = DateTime.Parse(dt.Rows[n]["LastCreateArchDate"].ToString());
                    }
                    model.LastCreateArchEmp = dt.Rows[n]["LastCreateArchEmp"].ToString();
                    if (dt.Rows[n]["AchievementYear"].ToString() != "")
                    {
                        model.AchievementYear = int.Parse(dt.Rows[n]["AchievementYear"].ToString());
                    }
                    if (dt.Rows[n]["AchievementMonth"].ToString() != "")
                    {
                        model.AchievementMonth = int.Parse(dt.Rows[n]["AchievementMonth"].ToString());
                    }
                    model.FundManageType = dt.Rows[n]["FundManageType"].ToString();
                    if (dt.Rows[n]["OrderPlaceId"].ToString() != "")
                    {
                        model.OrderPlaceId = new Guid(dt.Rows[n]["OrderPlaceId"].ToString());
                    }
                    model.OrderPlace = dt.Rows[n]["OrderPlace"].ToString();
                    if (dt.Rows[n]["BillPlaceId"].ToString() != "")
                    {
                        model.BillPlaceId = new Guid(dt.Rows[n]["BillPlaceId"].ToString());
                    }
                    model.BillPlace = dt.Rows[n]["BillPlace"].ToString();
                    if (dt.Rows[n]["PayPlaceId"].ToString() != "")
                    {
                        model.PayPlaceId = new Guid(dt.Rows[n]["PayPlaceId"].ToString());
                    }
                    model.PayPlace = dt.Rows[n]["PayPlace"].ToString();
                    if (dt.Rows[n]["IsRecheck"].ToString() != "")
                    {
                        if ((dt.Rows[n]["IsRecheck"].ToString() == "1") || (dt.Rows[n]["IsRecheck"].ToString().ToLower() == "true"))
                        {
                            model.IsRecheck = true;
                        }
                        else
                        {
                            model.IsRecheck = false;
                        }
                    }
                    model.PayBankType = dt.Rows[n]["PayBankType"].ToString();
                    model.PayBankName = dt.Rows[n]["PayBankName"].ToString();
                    model.PayBankNo = dt.Rows[n]["PayBankNo"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetAllList()
        {
            return GetList("");
        }

        /// <summary>
        /// 获取付款金额
        /// </summary>
        /// <param name="ContractId">成交编号</param>
        /// <param name="FundName">开单款项名称</param>
        /// <returns>付款金额</returns>
        public static decimal GetPayedMoney(Guid ContractId, string FundName)
        {
            decimal dRet = 0;
            dRet = Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetPayedMoney(ContractId, FundName);
            return dRet;
        }

        public static DataSet GetPaidItemsExportAll(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            //int PageSize = 10;
            //int PageIndex = 1;
            string SearchObj = "ContractDate";

            string TableName = "v_Contract_FinanceDetailInfo";
            string OrderField = "v_Contract_FinanceDetailInfo.ContractDate DESC";
            //string FieldList = "AreaName," +
            //                    "PointDepart," +
            //                    "ContractCode," +
            //                    "ContractDate," +
            //                    "HouseAddress," +
            //                    "PointEmp," +
            //                    "DeparterManagerName," +
            //                    "MangerName," +
            //                    "MajordomoName," +
            //                    "StanCommision," +
            //                    "RealCommisionFinal," +
            //                    "CommisionFinal," +
            //                    "ConsultingFinal," +
            //                    "Point," +
            //                    "PayMoney," +
            //                    "ApportionRealCommisionFinal," +
            //                    "ApportionCommisionFinal," +
            //                    "ApportionConsultingFinal," +
            //                    "ApportionPay," +
            //                    "PayDate," +
            //                    "PayArchDate," +
            //                    "ContractArchDate," +
            //                    "ContractCompanyName";
            string TableFieldList = "Chenxin_Main.dbo.FN_GetBussinessByDepartID(PointDepartId) As AreaName," +
                                    "PointDepart," +
                                    "ContractCode," +
                                    "REPLACE(CONVERT(Varchar(10),ContractDate,111),'/','-') As ContractDate," +
                                    "HouseAddress," +
                                    "PointEmp," +
                                    "DeparterManagerName," +
                                    "MangerName," +
                                    "MajordomoName," +
                                    "RegionManagerName,"+
                                    "StanCommision," +
                                    "RealCommisionFinal," +
                                    "CommisionFinal," +
                                    "ConsultingFinal," +
                                    "Point," +
                                    "PayMoney*inorout as PayMoney," +
                                    "ApportionRealCommisionFinal," +
                                    "ApportionCommisionFinal," +
                                    "ApportionConsultingFinal," +
                                    "ApportionPay*inorout as ApportionPay," +
                                    "REPLACE(CONVERT(Varchar(10),PayDate,111),'/','-') As PayDate," +
                                    "PayArchDate," +
                                    "REPLACE(CONVERT(Varchar(10),ContractArchDate,111),'/','-') As ContractArchDate," +
                                    "ContractCompanyName";
            strWhere.Append(" bPayArch=1 ");
            strWhere.Append(" And FundName In ('上家佣金','下家佣金','上家退佣','下家退佣','上家咨询费','下家咨询费') ");
            //if (dicParams.ContainsKey("PageSize"))
            //{
            //    PageSize = Convert.ToInt32(dicParams["PageSize"]);
            //}
            //if (dicParams.ContainsKey("PageIndex"))
            //{
            //    PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            //}
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }

            if (dicParams.ContainsKey("DateType"))
            {
                if (dicParams["DateType"].Equals("10"))
                {
                    SearchObj = "ContractDate";
                }
                else if (dicParams["DateType"].Equals("20"))
                {
                    SearchObj = "PayDate";
                }
                if (dicParams.ContainsKey("StartTime"))
                {
                    strWhere.AppendFormat(" And {0}>='{1}'", SearchObj, DateTime.Parse(dicParams["StartTime"]));
                }
                if (dicParams.ContainsKey("EndTime"))
                {
                    strWhere.AppendFormat(" And {0}<'{1}'", SearchObj, DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
                }
            }

            if (dicParams.ContainsKey("ArcTime"))
            {
                strWhere.AppendFormat(" And PayArchYear ={0}", dicParams["ArcTime"].Substring(0, 4));
                strWhere.AppendFormat(" And PayArchMonth ={0}", dicParams["ArcTime"].Length > 6 ? dicParams["ArcTime"].Substring(5, 2).TrimStart('0') : dicParams["ArcTime"].Substring(5, 1));
            }
            if (dicParams.ContainsKey("ContractCityId"))
            {
                strWhere.AppendFormat(" And ContractCityId = '{0}'", dicParams["ContractCityId"]);
            }
            if (dicParams.ContainsKey("ContractCompanyId"))
            {
                strWhere.AppendFormat(" And ContractCompanyId = '{0}'", dicParams["ContractCompanyId"]);
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (ContractCode Like '%{0}%' Or HouseAddress Like '%{0}%')", dicParams["Keyword"]);
            }

            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = PointEmpId And CityId = '{0}')", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = PointEmpId And CompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = PointDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And PointDepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And PointEmpId = '{0}'", dicParams["UserId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" and  PointDepartId in (select DepartId from t_Business_EmpSetting where UserId='{0}' and SearchPaidItems=1)", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.AppendFormat(" and PointEmpId='{0}' ", dicParams["CurUserId"]);
            }
            if (dicParams.ContainsKey("type"))
            {
                strWhere.AppendFormat(" and BusinessType='{0}' ", dicParams["type"]);
            }

            string strSql = "select " + TableFieldList + " from " + TableName + " where " + strWhere.ToString() + " order by " + OrderField;

            return Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        }


        public static DataSet GetPaidItemsByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;
            string SearchObj = "ContractDate";

            string TableName = "v_Contract_FinanceDetailInfo";
            string OrderField = "v_Contract_FinanceDetailInfo.ContractDate DESC";
            string FieldList = "AreaName," +
                                "PointDepart," +
                                "ContractCode," +
                                "ContractDate," +
                                "HouseAddress," +
                                "PointEmp," +
                                "DeparterManagerName," +
                                "MangerName," +
                                "MajordomoName," +
                                "RegionManagerName," +
                                "StanCommision," +
                                "RealCommisionFinal," +
                                "CommisionFinal," +
                                "ConsultingFinal," +
                                "Point," +
                                "PayMoney," +
                                "ApportionRealCommisionFinal," +
                                "ApportionCommisionFinal," +
                                "ApportionConsultingFinal," +
                                "ApportionPay," +
                                "PayDate," +
                                "PayArchDate," +
                                "ContractArchDate," +
                                "ContractCompanyName";
            string TableFieldList = "Chenxin_Main.dbo.FN_GetBussinessByDepartID(PointDepartId) As AreaName," +
                                    "PointDepart," +
                                    "ContractCode," +
                                    "ContractDate," +
                                    "HouseAddress," +
                                    "PointEmp," +
                                    "DeparterManagerName," +
                                    "MangerName," +
                                    "MajordomoName," +
                                    "RegionManagerName,"+
                                    "StanCommision," +
                                    "RealCommisionFinal," +
                                    "CommisionFinal," +
                                    "ConsultingFinal," +
                                    "Point," +
                                    "PayMoney*inorout as PayMoney," +
                                    "ApportionRealCommisionFinal," +
                                    "ApportionCommisionFinal," +
                                    "ApportionConsultingFinal," +
                                    "ApportionPay*inorout as ApportionPay," +
                                    "PayDate," +
                                    "PayArchDate," +
                                    "ContractArchDate," +
                                    "ContractCompanyName";
            strWhere.Append(" bPayArch=1 ");
            //strWhere.Append(" And FundName In ('上家佣金','下家佣金','上家退佣','下家退佣','上家咨询费','下家咨询费') ");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }

            if (dicParams.ContainsKey("DateType"))
            {
                if (dicParams["DateType"].Equals("10"))
                {
                    SearchObj = "ContractDate";
                }
                else if (dicParams["DateType"].Equals("20"))
                {
                    SearchObj = "PayDate";
                }
                if (dicParams.ContainsKey("StartTime"))
                {
                    strWhere.AppendFormat(" And {0}>='{1}'", SearchObj, DateTime.Parse(dicParams["StartTime"]));
                }
                if (dicParams.ContainsKey("EndTime"))
                {
                    strWhere.AppendFormat(" And {0}<'{1}'", SearchObj, DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
                }
            }

            if (dicParams.ContainsKey("ArcTime"))
            {
                strWhere.AppendFormat(" And PayArchYear ={0}", dicParams["ArcTime"].Substring(0, 4));
                strWhere.AppendFormat(" And PayArchMonth ={0}", dicParams["ArcTime"].Length > 6 ? dicParams["ArcTime"].Substring(5, 2).TrimStart('0') : dicParams["ArcTime"].Substring(5, 1));
            }
            if (dicParams.ContainsKey("ContractCityId"))
            {
                strWhere.AppendFormat(" And ContractCityId = '{0}'", dicParams["ContractCityId"]);
            }
            if (dicParams.ContainsKey("ContractCompanyId"))
            {
                strWhere.AppendFormat(" And ContractCompanyId = '{0}'", dicParams["ContractCompanyId"]);
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (ContractCode Like '%{0}%' Or HouseAddress Like '%{0}%')", dicParams["Keyword"]);
            }

            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = PointEmpId And CityId = '{0}')", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = PointEmpId And CompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = PointDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And PointDepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And PointEmpId = '{0}'", dicParams["UserId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" and  PointDepartId in (select DepartId from t_Business_EmpSetting where UserId='{0}' and SearchPaidItems=1)", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.AppendFormat(" and PointEmpId='{0}' ", dicParams["CurUserId"]);
            }
            if (dicParams.ContainsKey("type"))
            {
                strWhere.AppendFormat(" and BusinessType='{0}' ", dicParams["type"]);
            }

            DataSet ds = Chenxin.Lib.Business.DAL.ContractFinanceBillInfo.GetPartList(PageSize, PageIndex, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);

            //*************获取总合计数*****************
            string strSql = "select sum(isnull(Point,0)) as Point,sum(isnull(ApportionRealCommisionFinal,0)) as ApportionRealCommisionFinal,sum(isnull(ApportionCommisionFinal,0)) as ApportionCommisionFinal,sum(isnull(ApportionConsultingFinal,0)) as ApportionConsultingFinal,sum(isnull(ApportionPay*inorout,0)) as ApportionPay from " + TableName + " where " + strWhere.ToString();
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["AreaName"] = "总合计";
            dr["Point"] = ds_count.Tables[0].Rows[0]["Point"];
            //dr["ApportionRealCommisionFinal"] = ds_count.Tables[0].Rows[0]["ApportionRealCommisionFinal"];
            //dr["ApportionCommisionFinal"] = ds_count.Tables[0].Rows[0]["ApportionCommisionFinal"];
            //dr["ApportionConsultingFinal"] = ds_count.Tables[0].Rows[0]["ApportionConsultingFinal"];
            dr["ApportionPay"] = ds_count.Tables[0].Rows[0]["ApportionPay"];
            ds.Tables[0].Rows.Add(dr);
            //*****************************************

            return ds;
        }
        #endregion  成员方法
    }
}

